package com.cn.wanxi.util;

import com.cn.wanxi.model.UserModel;

import java.sql.*;
import java.util.List;
import java.util.Properties;

/**
 * jdbc的编写步骤
 * 1.加载驱动
 * 2.创建链接
 * 3.编译sql语句
 * 4.执行sql语句
 * 5.返回结果集
 * 6.关闭链接
 */
public class JDBC {

    private JDBC() {
    }

    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //在这里为什么不用直接去得到链接，而是初始化一个null,
    private static Connection connection = null;
    private static PreparedStatement preparedStatement = null;


    /**
     * 得到连接
     *
     * @return
     */
    private static Connection getConnection() {
//        Properties properties=new Properties();
        String url = "jdbc:mysql://121.199.55.96:3306/canyin04";
        String username = "canyin04";
        String password = "123456";
        try {
            if (connection == null) {
                connection = DriverManager.getConnection(url, username, password);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static void close() {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 查询
     *
     * @param sql
     */
    public static ResultSet query(String sql) {
//        Statement
//        PreparedStatement
        getConnection();
        try {
            preparedStatement = connection.prepareStatement(sql);
            return preparedStatement.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 返回影响数据库的条数
     *
     * @param sql
     * @return
     */
    public static int update(String sql, List<String> list) {
//        ？叫做占位符

        getConnection();
        try {
//            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < list.size(); i++
            ) {
                preparedStatement.setObject(i + 1, list.get(i));
            }
//            preparedStatement.setObject(1,userModel.getUsername());
//            preparedStatement.setObject(2,userModel.getPassword());
//            preparedStatement.setObject(3,userModel.getSex());
//            preparedStatement.executeUpdate();
//            connection.commit();手动提交
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return 0;
    }

    public static int update(String sql) {


        getConnection();
        try {
            preparedStatement = connection.prepareStatement(sql);
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
//            close();
        }
        return 0;
    }
}
